package dao;

import po.*;
import utils.DBUtils;
import vo.CartItemVo;
import vo.OrderItemVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class CartItemDao {
    public CartItem selectByUserIdAndProduct(CartItem cartItem){
        CartItem cart = null;/** 为什么先定义赋值为空？为了在finally中关闭这些*/
        Connection connection = DBUtils.getConn();
        String sql ="select * from tb_cart_item where user_id=? and product=?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,cartItem.getUserId());
            preparedStatement.setString(2,cartItem.getProduct());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                cart = new CartItem();
                cart.setUserId(resultSet.getString("user_id"));
                cart.setProduct(resultSet.getString("product"));
                cart.setCount(resultSet.getInt("count"));
                cart.setRid(resultSet.getInt("rid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return cart;
    }

    public Integer insert(CartItem cartItem) {
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        String sql ="insert into tb_cart_item values(null ,?,?,?)";
        Integer row = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,cartItem.getUserId());
            preparedStatement.setString(2,cartItem.getProduct());
            preparedStatement.setInt(3,cartItem.getCount());
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
        return row;
    }

    public Integer update(CartItem cartItem1) {
        Integer row = null;
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        String sql ="update tb_cart_item set count =? where user_id=? and product=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,cartItem1.getCount());
            preparedStatement.setString(2,cartItem1.getUserId());
            preparedStatement.setString(3,cartItem1.getProduct());
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
        return row;
    }
    public ArrayList<CartItemVo> selectByUserAssociatedBook(User user){
        ArrayList<CartItemVo> list = new ArrayList<CartItemVo>();
        Book book = null;
        CartItemVo cartItemVo = null;
        CartItem cartItem = null;//购物车表中，用户id，图书编号，数量
        Connection connection = DBUtils.getConn();
        String sql = "select book.*,item.count,item.user_id,item.rid " +
                "from tb_cart_item item inner join tb_book book " +
                "on item.product = book.isbn " +
                "where item.user_id=?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getPhone());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                //先把书的属性拿出来封装到book中
                book = new Book();
                book.setIsbn(resultSet.getString("isbn"));
                book.setEdition(resultSet.getInt("edition"));
                book.setForm(resultSet.getString("form"));
                book.setFormat(resultSet.getString("format"));
                book.setAuthor(resultSet.getString("author"));
                book.setTitle(resultSet.getString("title"));
                book.setWords(resultSet.getInt("words"));
                book.setPrice(resultSet.getDouble("price"));
                book.setPublished(resultSet.getDate("published"));
                book.setPress(resultSet.getString("press"));
                book.setPages(resultSet.getInt("pages"));
                book.setPackaging(resultSet.getString("packaging"));
                //再将购物车的属性拿出来封装到cartItem中
                cartItem = new CartItem();
                cartItem.setCount(resultSet.getInt("count"));
                cartItem.setUserId(resultSet.getString("user_id"));
                cartItem.setRid(resultSet.getInt("rid"));
                //最后再将book和cartItemVo中
                cartItemVo = new CartItemVo();
                cartItemVo.setBook(book);
                cartItemVo.setCartItem(cartItem);
                //每循环一次就将一个cartItemVo对象封装到list
                list.add(cartItemVo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }
    public Integer delete(String userId,int rid){
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        Integer row = null;
        String sql = "delete from tb_cart_item where user_id=? and rid=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,userId);
            preparedStatement.setInt(2,rid);
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
        return row;

    }

    public void updateByRid(Integer rid, Integer count) {
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        Integer row = null;
        String sql = "update tb_cart_item set count=? where rid=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,count);
            preparedStatement.setInt(2,rid);
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
    }

    public ArrayList<HashMap<String,Object>> selectCartItemAssociatedBook(String[] itemIds) {
        String s = "select c.product,c.count,b.price from tb_cart_item c " +
                "inner join tb_book b on c.product=b.isbn where 1=1";//恒成立
        /**
         * String是不可变字符串，StringBuilder是可变字符串
         */
        StringBuilder sql = new StringBuilder(s);
        /**
         * select * from cart where id in (1,2,3,4,5,6,7,8);
         */
        Integer[] cartItemIdsValue = new Integer[itemIds.length];
        if (cartItemIdsValue.length>0){
            sql.append(" and c.rid in (");
            for (int i=0;i<itemIds.length;i++){
                sql.append("?");
                if (i<itemIds.length-1) {
                    sql.append(",");
                }
                cartItemIdsValue[i] = Integer.parseInt(itemIds[i]);
            }
            sql.append(")");
        }
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        HashMap<String,Object> map = null;
        ArrayList<HashMap<String,Object>> list = new ArrayList<HashMap<String, Object>>();
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            for (int i=0;i<cartItemIdsValue.length;i++){
                preparedStatement.setInt(i+1,cartItemIdsValue[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                map = new HashMap<String, Object>();
                map.put("product",resultSet.getString("product"));
                map.put("count",resultSet.getInt("count"));
                map.put("price",resultSet.getDouble("price"));
                list.add(map);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }

            }
            DBUtils.close(preparedStatement,connection);
        }
        /**
         *  一个sql要执行很多遍，每一遍的条件id都不同
         */
        return list;
    }
}
